/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     02/05/2013 08:27:30 p.m.                     */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CITA') and o.name = 'FK_CITA_CITA_FUNC_FUNCION1')
alter table CITA
   drop constraint FK_CITA_CITA_FUNC_FUNCION1
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CITA') and o.name = 'FK_CITA_CITA_FUNC_FUNCION2')
alter table CITA
   drop constraint FK_CITA_CITA_FUNC_FUNCION2
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CITA') and o.name = 'FK_CITA_CITA_FUNC_FUNCION3')
alter table CITA
   drop constraint FK_CITA_CITA_FUNC_FUNCION3
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CITA') and o.name = 'FK_CITA_REFERENCE_SOLICITA')
alter table CITA
   drop constraint FK_CITA_REFERENCE_SOLICITA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CITA') and o.name = 'FK_CITA_REFERENCE_TIPO_TRA')
alter table CITA
   drop constraint FK_CITA_REFERENCE_TIPO_TRA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CITA') and o.name = 'FK_CITA_REFERENCE_ESTADO')
alter table CITA
   drop constraint FK_CITA_REFERENCE_ESTADO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CITA') and o.name = 'FK_CITA_REFERENCE_NOTARIA')
alter table CITA
   drop constraint FK_CITA_REFERENCE_NOTARIA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('FUNCIONARIO') and o.name = 'FK_FUNCIONA_REFERENCE_NOTARIA')
alter table FUNCIONARIO
   drop constraint FK_FUNCIONA_REFERENCE_NOTARIA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('FUNCIONARIO') and o.name = 'FK_FUNCIONA_REFERENCE_TIPO_FUN')
alter table FUNCIONARIO
   drop constraint FK_FUNCIONA_REFERENCE_TIPO_FUN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('INVOLUCRADO') and o.name = 'FK_INVOLUCR_REFERENCE_SOLICITA')
alter table INVOLUCRADO
   drop constraint FK_INVOLUCR_REFERENCE_SOLICITA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('INVOLUCRADO') and o.name = 'FK_INVOLUCR_REFERENCE_CITA')
alter table INVOLUCRADO
   drop constraint FK_INVOLUCR_REFERENCE_CITA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('NOTARIA') and o.name = 'FK_NOTARIA_REFERENCE_CIUDAD')
alter table NOTARIA
   drop constraint FK_NOTARIA_REFERENCE_CIUDAD
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('NOTARIA') and o.name = 'FK_NOTARIA_REFERENCE_NOTARIO')
alter table NOTARIA
   drop constraint FK_NOTARIA_REFERENCE_NOTARIO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CITA')
            and   type = 'U')
   drop table CITA
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CIUDAD')
            and   type = 'U')
   drop table CIUDAD
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ESTADO')
            and   type = 'U')
   drop table ESTADO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('FUNCIONARIO')
            and   type = 'U')
   drop table FUNCIONARIO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('INVOLUCRADO')
            and   type = 'U')
   drop table INVOLUCRADO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('NOTARIA')
            and   type = 'U')
   drop table NOTARIA
go

if exists (select 1
            from  sysobjects
           where  id = object_id('NOTARIO')
            and   type = 'U')
   drop table NOTARIO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SOLICITANTE')
            and   type = 'U')
   drop table SOLICITANTE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TIPO_FUNCIONARIO')
            and   type = 'U')
   drop table TIPO_FUNCIONARIO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TIPO_TRAMITE')
            and   type = 'U')
   drop table TIPO_TRAMITE
go

/*==============================================================*/
/* Table: CITA                                                  */
/*==============================================================*/
create table CITA (
   IDCITA               numeric              identity,
   IDSOLICITANTE        numeric              null,
   IDNOTARIA            numeric              null,
   IDLIQUIDADOR         numeric              null,
   IDFUNCIONARIO        numeric              null,
   IDTIPOTRAMITE        numeric              null,
   IDESTADO             numeric              null,
   IDASIGNADOR          numeric              null,
   FECHACITA            datetime             null,
   OBSERVACIONFUNCIONARIO varchar(500)         null,
   OBSERVACIONNOTARIO   varchar(500)         null,
   OBSERVACIONLIQUIDADOR varchar(500)         null,
   DOCSANEXADOS         varchar(500)         null,
   constraint PK_CITA primary key (IDCITA)
)
go

/*==============================================================*/
/* Table: CIUDAD                                                */
/*==============================================================*/
create table CIUDAD (
   IDCIUDAD             numeric              identity,
   DESCRIPCION          varchar(100)         null,
   constraint PK_CIUDAD primary key (IDCIUDAD)
)
go

/*==============================================================*/
/* Table: ESTADO                                                */
/*==============================================================*/
create table ESTADO (
   IDESTADO             numeric              identity,
   CODIGO               varchar(5)           null,
   DESCRIPCION          varchar(100)         null,
   constraint PK_ESTADO primary key (IDESTADO)
)
go

/*==============================================================*/
/* Table: FUNCIONARIO                                           */
/*==============================================================*/
create table FUNCIONARIO (
   IDFUNCIONARIO        numeric              identity,
   IDNOTARIA            numeric              null,
   IDTIPOFUNCIONARIO    numeric              null,
   NOMBRES              varchar(100)         null,
   constraint PK_FUNCIONARIO primary key (IDFUNCIONARIO)
)
go

/*==============================================================*/
/* Table: INVOLUCRADO                                           */
/*==============================================================*/
create table INVOLUCRADO (
   IDINVOLUCRADO        numeric              identity,
   IDSOLICITANTE        numeric              null,
   IDCITA               numeric              null,
   constraint PK_INVOLUCRADO primary key (IDINVOLUCRADO)
)
go

/*==============================================================*/
/* Table: NOTARIA                                               */
/*==============================================================*/
create table NOTARIA (
   IDNOTARIA            numeric              identity,
   IDNOTARIO            numeric              null,
   IDCIUDAD             numeric              null,
   RAZONSOCIAL          varchar(100)         null,
   DIRECION             varchar(100)         null,
   constraint PK_NOTARIA primary key (IDNOTARIA)
)
go

/*==============================================================*/
/* Table: NOTARIO                                               */
/*==============================================================*/
create table NOTARIO (
   IDNOTARIO            numeric              identity,
   NOMBRES              varchar(100)         null,
   constraint PK_NOTARIO primary key (IDNOTARIO)
)
go

/*==============================================================*/
/* Table: SOLICITANTE                                           */
/*==============================================================*/
create table SOLICITANTE (
   IDSOLICITANTE        numeric              identity,
   NOMBRES              varchar(100)         null,
   IDENTIFIACION        varchar(30)          null,
   EMAIL                varchar(100)         null,
   constraint PK_SOLICITANTE primary key (IDSOLICITANTE)
)
go

/*==============================================================*/
/* Table: TIPO_FUNCIONARIO                                      */
/*==============================================================*/
create table TIPO_FUNCIONARIO (
   IDTIPOFUNCIONARIO    numeric              identity,
   CODIGO               varchar(5)           null,
   DESCRIPCION          varchar(100)         null,
   constraint PK_TIPO_FUNCIONARIO primary key (IDTIPOFUNCIONARIO)
)
go

/*==============================================================*/
/* Table: TIPO_TRAMITE                                          */
/*==============================================================*/
create table TIPO_TRAMITE (
   IDTIPOTRAMITE        numeric              identity,
   DESCRIPCION          varchar(100)         null,
   COSTO                bigint               null,
   DOCUMENTACIONREQUERIDA varchar(300)         null,
   constraint PK_TIPO_TRAMITE primary key (IDTIPOTRAMITE)
)
go

alter table CITA
   add constraint FK_CITA_CITA_FUNC_FUNCION1 foreign key (IDLIQUIDADOR)
      references FUNCIONARIO (IDFUNCIONARIO)
go

alter table CITA
   add constraint FK_CITA_CITA_FUNC_FUNCION2 foreign key (IDFUNCIONARIO)
      references FUNCIONARIO (IDFUNCIONARIO)
go

alter table CITA
   add constraint FK_CITA_CITA_FUNC_FUNCION3 foreign key (IDASIGNADOR)
      references FUNCIONARIO (IDFUNCIONARIO)
go

alter table CITA
   add constraint FK_CITA_REFERENCE_SOLICITA foreign key (IDSOLICITANTE)
      references SOLICITANTE (IDSOLICITANTE)
go

alter table CITA
   add constraint FK_CITA_REFERENCE_TIPO_TRA foreign key (IDTIPOTRAMITE)
      references TIPO_TRAMITE (IDTIPOTRAMITE)
go

alter table CITA
   add constraint FK_CITA_REFERENCE_ESTADO foreign key (IDESTADO)
      references ESTADO (IDESTADO)
go

alter table CITA
   add constraint FK_CITA_REFERENCE_NOTARIA foreign key (IDNOTARIA)
      references NOTARIA (IDNOTARIA)
go

alter table FUNCIONARIO
   add constraint FK_FUNCIONA_REFERENCE_NOTARIA foreign key (IDNOTARIA)
      references NOTARIA (IDNOTARIA)
go

alter table FUNCIONARIO
   add constraint FK_FUNCIONA_REFERENCE_TIPO_FUN foreign key (IDTIPOFUNCIONARIO)
      references TIPO_FUNCIONARIO (IDTIPOFUNCIONARIO)
go

alter table INVOLUCRADO
   add constraint FK_INVOLUCR_REFERENCE_SOLICITA foreign key (IDSOLICITANTE)
      references SOLICITANTE (IDSOLICITANTE)
go

alter table INVOLUCRADO
   add constraint FK_INVOLUCR_REFERENCE_CITA foreign key (IDCITA)
      references CITA (IDCITA)
go

alter table NOTARIA
   add constraint FK_NOTARIA_REFERENCE_CIUDAD foreign key (IDCIUDAD)
      references CIUDAD (IDCIUDAD)
go

alter table NOTARIA
   add constraint FK_NOTARIA_REFERENCE_NOTARIO foreign key (IDNOTARIO)
      references NOTARIO (IDNOTARIO)
go

